package com.auxiliary.upload_download;

import java.io.File;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.myidis.entity.QuotaData;
import com.myidis.request.SearchReq;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class Download {
    public static File download(SearchReq sea)
            throws IOException, RowsExceededException, WriteException, SQLException {
        //1:创建excel文件
    	File file = new File("D:\\迅雷下载" + File.separator + "test.xls");
        file.createNewFile();
        
        //2:创建工作簿
        WritableWorkbook workbook=Workbook.createWorkbook(file);
        //3:创建sheet,设置第二三四..个sheet，依次类推即可
        WritableSheet sheet=workbook.createSheet("用户管理", 0);
        //4：设置titles 
        String titles[]  = null;
        titles = new String[] {"时间","指标","数据类型","频度","数值","单位"};
        //5:单元格
        Label label=null;
        //6:给第一行设置列名
        for(int i=0;i<titles.length;i++){
            //x,y,第一行的列名
            label=new Label(i,0,titles[i]);
            //7：添加单元格
            sheet.addCell(label);
        }

     // 8：从数据库导入数据
        Connection conn = ConnectDB.getConn();
        String sql = "SELECT time,quota,dataitem,frequency,value,unit FROM quota_data where quota = ? and dataitem = ? and frequency = ? and time >= ? and time <= ?";
        System.out.println(sql);
        PreparedStatement pstmt;
        pstmt = (PreparedStatement)conn.prepareStatement(sql);
        pstmt.setString(1, sea.getCondition());
        pstmt.setInt(2, sea.getKeyNum1());
        pstmt.setInt(3, sea.getKeyNum2());
        pstmt.setString(4, sea.getKeyWord1());
        pstmt.setString(5, sea.getKeyWord2());
        ResultSet rs = pstmt.executeQuery();
        int col = rs.getMetaData().getColumnCount();
        for (int j = 1; rs.next(); j++) {
            for (int i = 1; i <= col; i++) {
                label=new Label(i-1,j,rs.getString(i));
                sheet.addCell(label);
            }
        }
        //写入数据，一定记得写入数据，不然你都开始怀疑世界了，excel里面啥都没有
        workbook.write();
        //最后一步，关闭工作簿
        workbook.close();
        return file;
    }
}

